const db = require("../db");
const crypto = require("crypto");
const Student = require("./Student");
const Teacher = require("./Teacher");
const RootUser = require("./RootUser");
const Courses = require("./Courses");
const Scores = require("./Scores");
const xlsx = require("xlsx");
// //文章与用户一对一
// Article.belongsTo(Student, { targetKey: "id", foreignKey: "userId" });
// Student.hasOne(Article);
// //文章与评论一对多
// Article.hasMany(Comment);
// Comment.belongsTo(Article, { targetKey: "id", foreignKey: "articleId" });
// // 用户与评论一对多
// Student.hasMany(Comment);
// Comment.belongsTo(Student, { targetKey: "id", foreignKey: "userId" });

// 老师与课程
Teacher.hasMany(Courses, { targetKey: "id", foreignKey: "teacherId" });
Courses.belongsTo(Teacher);
// 学生与成绩
Student.hasMany(Scores, { targetKey: "id", foreignKey: "studentId" });
Scores.belongsTo(Student);
// 课程与成绩
Courses.hasMany(Scores, { targetKey: "id", foreignKey: "courseId" });
Scores.belongsTo(Courses);

(async () => {
  try {
    // Teacher.hasOne(Courses);
    await db.sync({ force: true });
  } catch (err) {
    console.log("错误2：" + err.message);
  }
  await addRoot();
  await addStudent();
  await addSTeacher();
  Excel_ScoresInsert("../public/xlsx/scores.xlsx");
})();

//添加管理员
async function addRoot() {
  await RootUser.create({
    userId: "admin", //学号
    password: "admin" //密码
  });
}
//添加学生
async function addStudent() {
  // 这里是代码
  let md5 = crypto.createHash("md5");
  let password = md5.update("123").digest("hex");
  await Student.create({
    sNum: "20202101", //学号
    password: password, //密码
    grade: "三年级", //年级
    classes: "2班", //班级
    name: "小文", //姓名
    sex: 0, //性别
    nation: "汉族", //民族
    birthdate: "2020-09-10", //出生期日
    census: "广西合浦县", //户籍
    address: "山口镇高坡村", //地址
    parent1: "文爸爸", //家长1
    phone1: "13978947932", //手机1
    parent2: "文妈妈", //家长2
    phone2: "15777915048" //手机2
  });

  await Student.create({
    sNum: "20202102", //学号
    password: crypto
      .createHash("md5")
      .update("123")
      .digest("hex"), //密码
    grade: "二年级", //年级
    classes: "1班", //班级
    name: "小豪", //姓名
    sex: 1, //性别
    nation: "汉族", //民族
    birthdate: "2020-09-10", //出生期日
    census: "广西合浦县", //户籍
    address: "山口镇高坡村", //地址
    parent1: "文爸爸", //家长1
    phone1: "13978947932", //手机1
    parent2: "文妈妈", //家长2
    phone2: "15777915048" //手机2
  });
  await Student.create({
    sNum: "20201102", //学号
    password: crypto
      .createHash("md5")
      .update("123")
      .digest("hex"), //
    grade: "一年级", //年级
    classes: "1班", //班级
    name: "小是", //姓名
    sex: 0, //性别
    nation: "汉族", //民族
    birthdate: "2020-09-10", //出生期日
    census: "广西合浦县", //户籍
    address: "山口镇高坡村", //地址
    parent1: "文爸爸", //家长1
    phone1: "13978947932", //手机1
    parent2: "文妈妈", //家长2
    phone2: "15777915048" //手机2
  });

  await Student.create({
    sNum: "20201103", //学号
    password: crypto
      .createHash("md5")
      .update("123")
      .digest("hex"), //
    grade: "一年级", //年级
    classes: "1班", //班级
    name: "小手动", //姓名
    sex: 1, //性别
    nation: "汉族", //民族
    birthdate: "2020-09-10", //出生期日
    census: "广西合浦县", //户籍
    address: "山口镇高坡村", //地址
    parent1: "文爸爸", //家长1
    phone1: "13978947932", //手机1
    parent2: "文妈妈", //家长2
    phone2: "15777915048" //手机2
  });
}
// 添加老师
async function addSTeacher() {
  // 这里是代码

  await Teacher.create({
    tNum: "202145", //学号
    password: crypto
      .createHash("md5")
      .update("123")
      .digest("hex"), //, //密码
    grade: "三年级", //年级
    name: "林老师", //姓名
    sex: 0, //性别
    position: "教师", //职位
    identity: "中共党员", //政治面貌
    subject: "英语", //学科
    nation: "汉族", //民族
    birthdate: "1994-09-19", //出生期日
    census: "广西合浦县", //户籍
    phone: "18734588353", //手机
    address: "合浦县山口镇" //地址
  });
  await Teacher.create({
    tNum: "202146", //学号
    password: crypto
      .createHash("md5")
      .update("123")
      .digest("hex"), //, //密码
    grade: "四年级", //年级
    name: "花老师", //姓名
    sex: 1, //性别
    position: "班主任", //职位
    identity: "中共党员", //政治面貌
    subject: "语文", //学科
    nation: "汉族", //民族
    birthdate: "1994-09-19", //出生期日
    census: "广西合浦县", //户籍
    phone: "18734588353", //手机
    address: "合浦县山口镇" //地址
  });
}

// 成绩——xlsx表格插入数据库
async function Excel_ScoresInsert(filepath) {
  //读取内容
  const workbook = xlsx.readFile(filepath);
  const sheetNames = workbook.SheetNames; //获取表名
  const sheet = workbook.Sheets[sheetNames[0]]; //通过表名得到表对象
  const thead = [
    sheet.A1.v,
    sheet.B1.v,
    sheet.C1.v,
    sheet.D1.v,
    sheet.E1.v,
    sheet.F1.v,
    sheet.G1.v,
    sheet.H1.v,
    sheet.I1.v,
    sheet.J1.v
  ];
  const data = xlsx.utils.sheet_to_json(sheet); //通过工具将表对象的数据读出来并转成json
  const theadRule = [
    "序号",
    "学期",
    "年级",
    "班级",
    "考试",
    "科目",
    "学号",
    "姓名",
    "成绩",
    "备注"
  ];
  const isValid = thead.every((value, index) => value === theadRule[index]); //检验表字段
  if (!isValid) {
    console.log("校验不正确！");
    return false;
  }

  let params = [];
  for (let i = 0; i < data.length; i++) {
    params.push({
      num: data[i][thead[0]],
      term: data[i][thead[1]],
      grade: data[i][thead[2]],
      classes: data[i][thead[3]],
      exam: data[i][thead[4]],
      subject: data[i][thead[5]],
      sNum: data[i][thead[6]],
      sName: data[i][thead[7]],
      scores: data[i][thead[8]],
      msg: data[i][thead[9]]
    });
  }
  // console.log(params);
  var scores = await Scores.bulkCreate(params);
  console.log(scores);
}
